#! /bin/bash

#上个月1日
Last_Month_DATE=$(date -d "$(date +%Y%m)01 last month" +%Y-%m-01)

out_put=$(${HIVE_HOME} -S -e "
--分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;
--分桶
set hive.enforce.bucketing=true;
set hive.enforce.sorting=true;
set hive.optimize.bucketmapjoin = true;
set hive.auto.convert.sortmerge.join=true;
set hive.auto.convert.sortmerge.join.noconditionaltask=true;
--并行执行
set hive.exec.parallel=true;
set hive.exec.parallel.thread.number=8;
--矢量化查询
set hive.vectorized.execution.enabled=true;
--关联优化器
set hive.optimize.correlation=true;
--读取零拷贝
set hive.exec.orc.zerocopy=true;

INSERT INTO itcast_dwd.customer_relationship_dwd PARTITION(payment_time_year, payment_time_month, payment_time_day)
SELECT
    id,
    deleted,
    customer_id,
    nvl(origin_type, -1) origin_type,
    payment_state,
    payment_time,
    substr(payment_time, 12, 2) as payment_time_hour,
    nvl(itcast_clazz_id, -1) itcast_clazz_id,
    nvl(creator, -1) creator,
    if(origin_type='NETSERVICE' or origin_type='PRESIGNUP', '1', '0') origin_type_stat,
    substr(payment_time, 1, 4) payment_time_year,
    substr(payment_time, 6, 2) payment_time_month,
    substr(payment_time, 9, 2) payment_time_day
from itcast_ods.customer_relationship
WHERE deleted = 0 AND customer_id IS NOT NULL AND payment_state='PAID'
and substr(payment_time, 1, 10) >='${Last_Month_DATE}';
")